﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace MINERP.DAL.Statistics
{
    public class ShopBillIndexDAL
    {
        //private string conStr = ConfigurationManager.ConnectionStrings["fffff"].ToString();
        private string conStr = DALBase.ConnStr;
        #region 所有商户
        /// <summary>
        /// 所有商户
        /// </summary>
        /// <returns></returns>
        public DataTable GetShops()
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "select ShopId,ShopName from shop  where status=1";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 时间段内的账单及商户信息
        /// <summary>
        /// 时间段内的账单及商户信息
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataSet GetBillsByTimeSpan(DateTime start, DateTime end,int shopId,int status)
        {
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                string sql = "SELECT shop_bill.BillId,max(BillOrderCode) as BillOrderCode, max(CreateTime) as CreateTime,max(TotalPrice) AS SumCount, Status";//,sum(area) as SumArea";
                sql += " FROM SHOP_Bill";// where shopid=" + shopId + " and createtime>'"+start+"' and createtime<='"+end+"' order by CreateTime ";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billid";
                sql += " inner join bill_detail_detail on bill_detail.billdetailid=bill_detail_detail.billdetailid";
                sql += " group by shopid,shop_bill.billId,Status having";
                sql += " shopid=" + shopId + " and max(createtime)>='" + start + "' and max(createtime)<'" + end.AddDays(1) + "'";        //最后一句是为分组使用
                if (status != 0)                //要分状态
                {
                    sql += " and Status=" + status;
                }
                sql += "  order by max(CreateTime)";
                //sql += " and shop_bill.billid=bill_detailid and bill_detail.billdetailId=bill_detail_detail.billdetailid";
                string sql2 = "select ShopId,ShopName,ShopKeeper,cast('"+start+"' as datetime) as StartTime,cast('"+end+"' as datetime) as EndTime, Tel1 from shop where shopid="+shopId;
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlCommand cmd2 = new SqlCommand(sql2, con);
                DataTable dt = CreateBillDt();
                dt.TableName = "BillInfo";

                DataTable dt2 = new DataTable();
                dt2.TableName = "ShopInfo";
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                dt.Columns.Add("SumArea",typeof(double));
                //string sql3 = "select sum(area) from bill_detail_detail,bill_detail,shop_bill where bill_detail_detail.type=1 and bill_detail.billdetailid=bill_detail_detail.billdetailid";
                //sql3 += " and shop_bill.billid=bill_detail.billid and shop_bill.billid=";
                string sql3 = "select sum(area) from bill_detail_detail inner join bill_detail on bill_detail_detail.type=1 and bill_detail.billdetailid=bill_detail_detail.billdetailid inner join shop_bill on ";
                sql3 += " shop_bill.billid=bill_detail.billid where shop_bill.billid=";

                string sql4 = "";
                foreach (DataRow r in dt.Rows)
                {
                    int billId = Convert.ToInt32(r["BillId"]);
                    sql4 = sql3 + billId;
                    cmd.CommandText=sql4;
                    if (cmd.ExecuteScalar() != DBNull.Value)
                        r["SumArea"] = Convert.ToDouble(cmd.ExecuteScalar());
                    else
                        r["SumArea"] = 0;

                    int status2 = Convert.ToInt16(r["Status"]);
                    COM.ShopBillStatus billStatus = (COM.ShopBillStatus)status2;
                    switch (billStatus)
                    {
                        case MINERP.COM.ShopBillStatus.CheckedOut:
                            r["StatusStr"] = "已付款"; break;
                        case MINERP.COM.ShopBillStatus.UnCheckOut:
                            r["StatusStr"] = "未付款"; break;
                    }
                }

                adapter.SelectCommand = cmd2;
                adapter.Fill(dt2);
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                ds.Tables.Add(dt2);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 结账报表新华专用
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <param name="timeType"></param>
        /// <returns></returns>
        public DataSet GetBillStatistics(DateTime start, DateTime end, int status, int timeType)
        {
            DataSet dsResult = new DataSet();
            Database db = DatabaseFactory.CreateDatabase();
        
      
            DbCommand dbCommand = db.GetStoredProcCommand("BillStatistics");
            dbCommand.CommandTimeout = 60;
            db.AddInParameter(dbCommand, "startTime", DbType.DateTime, start);
            db.AddInParameter(dbCommand, "endTime", DbType.DateTime, end);
            db.AddInParameter(dbCommand, "flag", DbType.Int32, status);
            db.AddInParameter(dbCommand, "timeType", DbType.Int32, timeType);
            try
            {
                dsResult = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception)
            {
                return null;
            }

            return dsResult;
        }

        /// <summary>
        /// 结账报表（亿康）
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="status"></param>
        /// <param name="timeType"></param>
        /// <returns></returns>
        public DataSet GetBillProduct(DateTime start, DateTime end,int shopId)
        {
            DataSet dsResult = new DataSet();
            Database db = DatabaseFactory.CreateDatabase();


            DbCommand dbCommand = db.GetStoredProcCommand("spRpt_detail");
            dbCommand.CommandTimeout = 60;
            db.AddInParameter(dbCommand, "startTime", DbType.DateTime, start);
            db.AddInParameter(dbCommand, "endTime", DbType.DateTime, end);
            db.AddInParameter(dbCommand, "shopId", DbType.Int32, shopId);
            try
            {
                dsResult = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception)
            {
                return null;
            }

            return dsResult;
        }

        #endregion

        private DataTable CreateBillDt()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("BillId", typeof(int));
            dt.Columns.Add("BillOrderCode", typeof(string));
            dt.Columns.Add("CreateTime", typeof(DateTime));
            dt.Columns.Add("SumCount", typeof(double));
            dt.Columns.Add("Status", typeof(int));
            dt.Columns.Add("StatusStr", typeof(string));
            return dt;
        }

        public DataTable GetBillEditRecord(DateTime start, DateTime end)
        {
            DataSet dsResult = new DataSet();
            Database db = DatabaseFactory.CreateDatabase();


            DbCommand dbCommand = db.GetStoredProcCommand("sp_billEditRecords");
            dbCommand.CommandTimeout = 60;
            db.AddInParameter(dbCommand, "start", DbType.DateTime, start);
            db.AddInParameter(dbCommand, "end", DbType.DateTime, end);

            try
            {
                dsResult = db.ExecuteDataSet(dbCommand);
            }
            catch (Exception)
            {
                return null;
            }

            return dsResult.Tables[0];
        }
    }
}
